/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
//package javaapplication1;

import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Andrea
 */
public class DatabaseHelper {

    private static final String url = "jdbc:mysql://192.168.2.51:3306/";
    private static final String dbName = "game";
    private static final String driver = "com.mysql.jdbc.Driver";
    private static final String userName = "****";
    private static final String password = "********";
    private Connection vConnection;

    public int executeSQLQuery(String vQuery) {
        connect();
        try {
            PreparedStatement vPreparedStatement = vConnection.prepareStatement(vQuery);
            vPreparedStatement.execute();
            vPreparedStatement.close();

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        disconnect();
        return 0;
    }

    public ArrayList returnTeamDTO(XMLDTO vXMLDTO) {
        connect();
        ArrayList vResultList = new ArrayList();
        PreparedStatement vPreparedStatement = null;
        ResultSet vResultSet = null;
        try {
            vPreparedStatement = vConnection.prepareStatement("Select * FROM teams WHERE Team_Name=?");
            vPreparedStatement.setString(1, vXMLDTO.getTeam());
            vResultSet = vPreparedStatement.executeQuery();


            while (vResultSet.next()) {
                TeamDTO pTeamDTO = new TeamDTO();
                pTeamDTO.setID(vResultSet.getInt("ID"));
                pTeamDTO.setTeamName(vResultSet.getString("Team_Name"));
                pTeamDTO.setResources(vResultSet.getInt("Resources"));
                pTeamDTO.setTimeStamp(vResultSet.getTimestamp("Time_Stamp"));
                vResultList.add(pTeamDTO);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                vPreparedStatement.close();
                vResultSet.close();
                disconnect();
            } catch (SQLException ex) {
                Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return vResultList;
    }

    public ArrayList returnResourceDTO(XMLDTO vXMLDTO) {
        connect();
        ArrayList vResultList = new ArrayList();
        PreparedStatement vPreparedStatement = null;
        ResultSet vResultSet = null;
        try {
            //------------------------------------------------------------------
            if (vXMLDTO.isAlgorithm()){ 
                //prepare statement for algorithm
                vPreparedStatement = vConnection.prepareStatement("Select * FROM resources WHERE HasResources=?");
                vPreparedStatement.setBoolean(1, false);
            }else{//if user request
                //prepare statement for user request
                vPreparedStatement = vConnection.prepareStatement("Select * FROM resources WHERE Grid_Row=? AND Grid_Col=?");
                vPreparedStatement.setInt(1, vXMLDTO.getRow());
                vPreparedStatement.setInt(2, vXMLDTO.getCol());
            }
            //------------------------------------------------------------------
            //vPreparedStatement = vConnection.prepareStatement("Select * FROM resources WHERE Grid_Row=? AND Grid_Col=?");
            //vPreparedStatement.setInt(1, vXMLDTO.getRow());
            //vPreparedStatement.setInt(2, vXMLDTO.getCol());
            vResultSet = vPreparedStatement.executeQuery();


            while (vResultSet.next()) {
                ResourceDTO pResourceDTO = new ResourceDTO();
                pResourceDTO.setID(vResultSet.getInt("ID"));
                pResourceDTO.setGridRow(vResultSet.getInt("Grid_Row"));
                pResourceDTO.setGridColumn(vResultSet.getInt("Grid_Col"));
                pResourceDTO.setHasResources(vResultSet.getBoolean("HasResources"));
                pResourceDTO.setAmount(vResultSet.getInt("Amount"));
                pResourceDTO.setTimeStamp(vResultSet.getTimestamp("Time_Stamp"));
                vResultList.add(pResourceDTO);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                vPreparedStatement.close();
                vResultSet.close();
                disconnect();
            } catch (SQLException ex) {
                Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return vResultList;
    }

    public ArrayList returnFortressDTO(int vFortress_Name) {
        connect();
        ArrayList vResultList = new ArrayList();

        PreparedStatement vPreparedStatement = null;
        ResultSet vResultSet = null;
        try {
            vPreparedStatement = vConnection.prepareStatement("Select * FROM fortress WHERE Fortress_Name=?");
            vPreparedStatement.setInt(1, vFortress_Name);
            vResultSet = vPreparedStatement.executeQuery();


            while (vResultSet.next()) {
                FortressDTO pFortressDTO = new FortressDTO();
                pFortressDTO.setID(vResultSet.getInt("ID"));
                pFortressDTO.setFortressName(vResultSet.getInt("Fortress_Name"));
                pFortressDTO.setStrengthLevel(vResultSet.getInt("Strength_Level"));
                pFortressDTO.setTimeStamp(vResultSet.getTimestamp("Time_Stamp"));
                vResultList.add(pFortressDTO);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                vPreparedStatement.close();
                vResultSet.close();
                //disconnect();
            } catch (SQLException ex) {
                Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return vResultList;
    }

    public ArrayList returnLandClaimDTO(XMLDTO vXMLDTO) {
        connect();
        ArrayList vResultList = new ArrayList();
        PreparedStatement vPreparedStatement = null;
        ResultSet vResultSet = null;
        try {
            vPreparedStatement = vConnection.prepareStatement("Select * FROM land_claim WHERE Grid_Row=? AND Grid_Col=?");
            vPreparedStatement.setInt(1, vXMLDTO.getRow());
            vPreparedStatement.setInt(2, vXMLDTO.getCol());
            vResultSet = vPreparedStatement.executeQuery();

            while (vResultSet.next()) {
                LandClaimDTO pLandClaimDTO = new LandClaimDTO();
                pLandClaimDTO.setID(vResultSet.getInt("ID"));
                pLandClaimDTO.setGridRow(vResultSet.getInt("Grid_Row"));
                pLandClaimDTO.setGridColumn(vResultSet.getInt("Grid_Col"));
                pLandClaimDTO.setFortressName(vResultSet.getInt("Fortress_Name"));
                pLandClaimDTO.setTeamName(vResultSet.getString("Team_Name"));
                pLandClaimDTO.setIsDestroyed(vResultSet.getBoolean("IsDestroyed"));
                pLandClaimDTO.setRedActive(vResultSet.getBoolean("Red_Active"));
                pLandClaimDTO.setBlueActive(vResultSet.getBoolean("Blue_Active"));
                pLandClaimDTO.setTimeStamp(vResultSet.getTimestamp("Time_Stamp"));
                vResultList.add(pLandClaimDTO);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                vPreparedStatement.close();
                vResultSet.close();
                disconnect();
            } catch (SQLException ex) {
                Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return vResultList;
    }

    private void connect() {
        try {
            vConnection = DriverManager.getConnection(url + dbName, userName, password);
       	    vConnection.settransactionisolation(0);
	 } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void disconnect() {
        try {
            vConnection.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void activate_block(XMLDTO vXMLDTO) {//for architect
        connect();
        PreparedStatement vPreparedStatement = null;
        String vQuery = null;
        vQuery = "INSERT INTO land_claim (Grid_Row, Grid_Col, Fortress_Name, Team_Name, ";
        vQuery = vQuery + "IsDestroyed, Red_Active, Blue_Active) values (?,?,?,?,?,?,?)";

        try {
            vPreparedStatement = vConnection.prepareStatement(vQuery);
            vPreparedStatement.setInt(1, vXMLDTO.getRow());
            vPreparedStatement.setInt(2, vXMLDTO.getCol());
            vPreparedStatement.setInt(3, -1);                //default fortress name value
            vPreparedStatement.setString(4, vXMLDTO.getTeam());
            vPreparedStatement.setBoolean(5, false);                //default is not destroyed
            if (vXMLDTO.getTeam().equals("Red")) {

                vPreparedStatement.setBoolean(6, true);                 //activated by red            
                vPreparedStatement.setBoolean(7, false);

            } else {//case of blue team
                vPreparedStatement.setBoolean(6, false);                 //activated by blue            
                vPreparedStatement.setBoolean(7, true);
            }// only two cases for a team
            vPreparedStatement.execute();
            vPreparedStatement.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        disconnect();
    }

    public void reactivate_block(XMLDTO vXMLDTO) {//for architect
        connect();
        PreparedStatement vPreparedStatement = null;
        try {
            if (vXMLDTO.getTeam() == "Red") {
                vPreparedStatement = vConnection.prepareStatement("UPDATE land_claim SET Red_Active=? WHERE Grid_Row=? AND Grid_Col=?");
            } else {//case of blue team
                vPreparedStatement = vConnection.prepareStatement("UPDATE land_claim SET Blue_Active=? WHERE Grid_Row=? AND Grid_Col=?");
            }
            vPreparedStatement.setBoolean(1, true);
            vPreparedStatement.setInt(2, vXMLDTO.getRow());
            vPreparedStatement.setInt(3, vXMLDTO.getCol());
            vPreparedStatement.execute();
            vPreparedStatement.close();

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        disconnect();

    }
    
    public void reactivate_resource(int vRow, int vColumn){
                connect();
        PreparedStatement vPreparedStatement = null;
        try {
            vPreparedStatement = vConnection.prepareStatement("UPDATE resources SET HasResources=?, Amount=? WHERE Grid_Row=? AND Grid_Col=?");
            vPreparedStatement.setBoolean(1, true);
            vPreparedStatement.setInt(2, 1000);
            vPreparedStatement.setInt(3, vRow);
            vPreparedStatement.setInt(4, vColumn);
            vPreparedStatement.execute();
            vPreparedStatement.close();

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        disconnect();
    }
    
    public void deactivate_resource(int vRow, int vColumn){
                connect();
        PreparedStatement vPreparedStatement = null;
        try {
            vPreparedStatement = vConnection.prepareStatement("UPDATE resources SET HasResources=?, Amount=? WHERE Grid_Row=? AND Grid_Col=?");
            vPreparedStatement.setBoolean(1, false);
            vPreparedStatement.setInt(2, 1000);
            vPreparedStatement.setInt(3, vRow);
            vPreparedStatement.setInt(4, vColumn);
            vPreparedStatement.execute();
            vPreparedStatement.close();

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        disconnect();
    }
    
      public void change_resource(String vTeam, int Amount){
        connect();
        PreparedStatement vPreparedStatement = null;
        try {
            vPreparedStatement = vConnection.prepareStatement("UPDATE teams SET Resources=? WHERE Team_Name=?");
            vPreparedStatement.setInt(1, Amount);
            vPreparedStatement.setString(2, vTeam);
            vPreparedStatement.execute();
            vPreparedStatement.close();

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        
       
    }
      
      
       public void capture_block(XMLDTO vXMLDTO){
        connect();
        PreparedStatement vPreparedStatement = null;
        try {
            vPreparedStatement = vConnection.prepareStatement("UPDATE land_claim SET Team=?, Red_Active=?, Blue_Acttive=?, IsDestroyed=? WHERE Grid_Row=? AND Grid_Col");
            vPreparedStatement.setString(1, vXMLDTO.getTeam());
            vPreparedStatement.setBoolean(1, false);
            vPreparedStatement.setBoolean(1, false);
            vPreparedStatement.setBoolean(1, false);
            vPreparedStatement.execute();
            vPreparedStatement.close();

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        
       
    }
       
       public void destroy_block(XMLDTO vXMLDTO){
        connect();
        PreparedStatement vPreparedStatement = null;
        try {
            vPreparedStatement = vConnection.prepareStatement("UPDATE land_claim SET Team=?, Red_Active=?, Blue_Acttive=?, IsDestroyed=? WHERE Grid_Row=? AND Grid_Col");
            vPreparedStatement.setString(1, "");
            vPreparedStatement.setBoolean(1, false);
            vPreparedStatement.setBoolean(1, false);
            vPreparedStatement.setBoolean(1, true);
            vPreparedStatement.execute();
            vPreparedStatement.close();

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        
       
    }
    
}
